﻿using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using XCLib.Log;

namespace DBUtility
{
    /// <summary>
    ///MySQLHelper 的摘要说明
    /// </summary>
    public class MySQLHelper
    {
        /// <summary>
        /// 数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
        /// </summary>
        public static string connectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
        /// <summary>
        /// 构造函数
        /// </summary>
        public MySQLHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="commandType">语句类型</param>
        /// <param name="commandText">语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, (MySqlParameter[])null);
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="commandType">语句类型</param>
        /// <param name="commandText">语句</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                int result = 0;
                try
                {
                    // if (connection == null) throw new ArgumentNullException("connection");
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = commandText;
                    cmd.CommandType = commandType;
                    if (commandParameters != null)
                        cmd.Parameters.AddRange(commandParameters);
                    result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                }
                catch (MySqlException ex)
                {
                    CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ExecuteNonQuery函数,执行语句:" + commandText);
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return result;
            }
        }

        #endregion


        #region ExecuteDataSet
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(commandType, commandText, (MySqlParameter[])null);
        }
        /// <summary>
        /// 执行查询命令
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = commandText;
                    cmd.CommandType = commandType;
                    if (commandParameters != null)
                        cmd.Parameters.AddRange(commandParameters);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(ds);
                    cmd.Dispose();
                }
                catch (MySqlException ex)
                {
                    CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ExecuteDataset函数,执行语句:" + commandText);
                    //throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string conn, CommandType commandType, string commandText)
        {
            return ExecuteDataset(conn, commandType, commandText, (MySqlParameter[])null);
        }
        /// <summary>
        /// 执行查询命令
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string conn, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            connectionString = ConfigurationManager.ConnectionStrings[conn].ToString();
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = commandText;
                    cmd.CommandType = commandType;
                    if (commandParameters != null)
                        cmd.Parameters.AddRange(commandParameters);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(ds);
                    cmd.Dispose();
                }
                catch (MySqlException ex)
                {
                    CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ExecuteDataset函数,执行语句:" + commandText);
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(commandType, commandText, (MySqlParameter[])null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlDataReader msd = null;
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = commandText;
                    cmd.CommandType = commandType;
                    if (commandParameters != null)
                        cmd.Parameters.AddRange(commandParameters);
                    msd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (MySqlException ex)
                {
                    CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ExecuteReader函数,执行语句:" + commandText);
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return msd;
            }
        }
        #endregion

        #region ExecuteScalar

        /// <summary>
        /// Execute a single command against a MySQL database.
        /// </summary>
        /// <param name="commandText">Command text to use for the update</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(string commandText)
        {
            //pass through the call providing null for the set of MySqlParameters
            return ExecuteScalar(commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// Execute a single command against a MySQL database.
        /// </summary>
        /// <param name="connectionString">Settings to use for the command</param>
        /// <param name="commandText">Command text to use for the command</param>
        /// <param name="commandParameters">Parameters to use for the command</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        //public static object ExecuteScalar(string commandText, params MySqlParameter[] commandParameters)
        //{
        //    //create & open a SqlConnection, and dispose of it after we are done.
        //    using (MySqlConnection connection = new MySqlConnection(connectionString))
        //    {
        //        connection.Open();

        //        //call the overload that takes a connection in place of the connection string
        //        return ExecuteScalar(commandText, commandParameters);
        //    }
        //}

        /// <summary>
        /// Execute a single command against a MySQL database.
        /// </summary>
        /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
        /// <param name="commandText">Command text to use for the command</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        //public static object ExecuteScalar(string commandText)
        //{
        //    //pass through the call providing null for the set of MySqlParameters
        //    return ExecuteScalar(commandText, (MySqlParameter[])null);
        //}

        /// <summary>
        /// Execute a single command against a MySQL database.
        /// </summary>
        /// <param name="commandText">Command text to use for the command</param>
        /// <param name="commandParameters">Parameters to use for the command</param>
        /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        public static object ExecuteScalar(string commandText, params MySqlParameter[] commandParameters)
        {
            //create a command and prepare it for execution
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                object retval = new object();
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = commandText;
                    cmd.CommandType = CommandType.Text;

                    if (commandParameters != null)
                        foreach (MySqlParameter p in commandParameters)
                            cmd.Parameters.Add(p);
                    //execute the command & return the results
                    // detach the SqlParameters from the command object, so they can be used again.
                    cmd.Parameters.Clear();
                    retval = cmd.ExecuteScalar();
                }
                catch (MySqlException ex)
                {
                    CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ExecuteScalar函数,执行语句:" + commandText);
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return retval;
            }
        }

        #endregion

        #region 数据集操作
        /// <summary>
        /// 此方法将所选字段的唯一值复制到一个新的 DataTable 。 如果字段包含 NULL 值，目标表中的记录还包含 NULL 值 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="SourceTable"></param>
        /// <param name="FieldName"></param>
        /// <returns></returns>
        public static DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
        {
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
            ////if (ds != null)
            ////ds.Tables.Add(dt);
            return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="A"></param>
        /// <param name="B"></param>
        /// <returns></returns>
        static bool ColumnEqual(object A, object B)
        {
            if (A == DBNull.Value && B == DBNull.Value)   //   both   are   DBNull.Value   
                return true;
            if (A == DBNull.Value || B == DBNull.Value)   //   only   one   is   DBNull.Value   
                return false;
            return (A.Equals(B));   //   value   type   standard   comparison   
        }

        /// <summary>
        /// 此方法将所选字段的唯一值复制到一个新的 DataTable 。 如果字段包含 NULL 值，目标表中的记录还包含 NULL 值 
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="SourceTable">原数据集</param>
        /// <param name="FieldName">字段名称</param>
        /// <param name="FieldValue">字段值</param>
        /// <returns></returns>
        public static DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName, string FieldValue)
        {
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    if (LastValue.ToString() == FieldValue)
                    {
                        dt.Rows.Add(new object[] { LastValue });
                    }
                }
            }
            ////if (ds != null)
            ////ds.Tables.Add(dt);
            return dt;
        }

        /// <summary>
        /// 将所选字段的唯一值复制到一个新的 DataTable 。 如果字段包含 NULL 值，目标表中的记录还包含 NULL 值 
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="SourceTable">原数据集</param>
        /// <param name="FieldName">字段名称</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public static DataTable SelectDistinctWhere(string TableName, DataTable SourceTable, string FieldName, string strWhere)
        {
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select(strWhere, FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
            ////if (ds != null)
            ////ds.Tables.Add(dt);
            return dt;
        }
        #endregion

        #region 公共操作
        /// <summary>
        /// 调用存储过程获取统计数据
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="equipID">设备ID</param>
        /// <param name="sTime">开始事件</param>
        /// <param name="eTime">结束时间</param>
        /// <param name="pIsTotal">统计类型</param>
        /// <returns></returns>
        public static DataSet getDataSet(string conn, string procedureName, string equipID, string sTime, string eTime, string pIsTotal)
        {
            CommandType commtype = CommandType.StoredProcedure;
            //设置存储过程参数
            MySqlParameter[] mySqlPar = new MySqlParameter[4];
            mySqlPar[0] = new MySqlParameter("p_ne_id", MySqlDbType.VarChar);
            mySqlPar[0].Value = equipID;
            mySqlPar[1] = new MySqlParameter("p_stime", MySqlDbType.DateTime);
            mySqlPar[1].Value = Convert.ToDateTime(sTime);
            mySqlPar[2] = new MySqlParameter("p_etime", MySqlDbType.DateTime);
            mySqlPar[2].Value = Convert.ToDateTime(eTime);
            mySqlPar[3] = new MySqlParameter("p_is_total", MySqlDbType.VarChar);
            mySqlPar[3].Value = pIsTotal;
            //调用函数执行存储过程
            DataSet ds = MySQLHelper.ExecuteDataset(conn, commtype, procedureName, mySqlPar);
            return ds;
        }
        /// <summary>
        /// 调用存储过程获取统计数据
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="count">记录前数目</param>
        /// <param name="sTime">开始事件</param>
        /// <param name="eTime">结束时间</param>
        /// <returns></returns>
        public static DataSet getDataSetTop(string conn, string procedureName, string count, string sTime, string eTime)
        {
            CommandType commtype = CommandType.StoredProcedure;
            //设置存储过程参数
            MySqlParameter[] mySqlPar = new MySqlParameter[3];

            mySqlPar[0] = new MySqlParameter("p_stime", MySqlDbType.DateTime);
            mySqlPar[0].Value = Convert.ToDateTime(sTime);
            mySqlPar[1] = new MySqlParameter("p_etime", MySqlDbType.DateTime);
            mySqlPar[1].Value = Convert.ToDateTime(eTime);
            mySqlPar[2] = new MySqlParameter("p_top", MySqlDbType.Int32);
            mySqlPar[2].Value = Convert.ToInt32(count);
            //调用函数执行存储过程
            DataSet ds = MySQLHelper.ExecuteDataset(conn, commtype, procedureName, mySqlPar);
            return ds;
        }
        /// <summary>
        /// 转换数据，将数据集列行转换
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="dt">原数据集</param>
        /// <param name="colName">字段名称</param>
        /// <param name="reportid">报表类型ID</param>
        /// <returns></returns>
        public static DataTable ConvertTable(string conn, DataTable dt, string colName, string reportid)
        {
            DataTable dtNew = GetTable(colName);
            try
            {
                int rCount = dt.Rows.Count;//行数
                // int cCount = dt.Columns.Count;//列数           

                DataSet ds = GetIndicatorsData(conn, Convert.ToInt32(reportid));
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    int length = ds.Tables[0].Rows.Count;
                    for (int i = 0; i < length; i++)
                    {
                        for (int k = 0; k < rCount; k++)
                        {
                            DataRow dr = dtNew.NewRow();
                            dr[0] = dt.Rows[k][colName].ToString();
                            dr[1] = ds.Tables[0].Rows[i][2].ToString();
                            dr[2] = ds.Tables[0].Rows[i][3].ToString();
                            dr[3] = dt.Rows[k][ds.Tables[0].Rows[i][3].ToString()].ToString();
                            dtNew.Rows.Add(dr);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                CLogSingleton.PolicyOneLog("错误:" + ex.Message, "", DateTime.Now.ToString() + "\r\n运行DBUtility.MySQLHelper.ConvertTable函数.");
            }
            return dtNew;
        }
        /// <summary>
        /// 创建转换表
        /// </summary>
        /// <param name="colName">字段名称</param>
        /// <returns></returns>
        public static DataTable GetTable(string colName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(colName);
            dt.Columns.Add("IndicatorsCN");
            dt.Columns.Add("Indicators");
            dt.Columns.Add("IndicatorsValue");
            return dt;
        }
        /// <summary>
        /// 获取指标数据集
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="reportid">报表类型ID</param>
        public static DataSet GetIndicatorsData(string conn, int reportid)
        {
            CommandType commtype = CommandType.StoredProcedure;
            //存储过程参数
            MySqlParameter[] mySqlPar = new MySqlParameter[1];
            mySqlPar[0] = new MySqlParameter("reporttype", MySqlDbType.Int32);
            mySqlPar[0].Value = reportid;
            //调用函数执行存储过程
            return MySQLHelper.ExecuteDataset(conn, commtype, "report_get_map", mySqlPar);
        }
        #endregion
    }

}